package bd;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class DataBaseConection {
	
	private Connection conn;
	private static DataBaseConection dataBaseConection;

	private DataBaseConection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// Con un tunel se puede trabajar desde las casas y no hace falta estar
			// fisicamente en los laboratorios del ITBA
			String url = "jdbc:oracle:thin:@eiffel.it.itba.edu.ar:1521:ITBA";
			conn = DriverManager.getConnection(url, "gmarucci", "itba49881181");
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static DataBaseConection getInstance() {
		if (dataBaseConection == null) {
			dataBaseConection = new DataBaseConection();
		}
		return dataBaseConection;
	}
	
	public synchronized void printDate() throws SQLException {
		Statement stmt = conn.createStatement();
		ResultSet resultSet = stmt.executeQuery("select sysdate from dual");
		resultSet.next();
		System.out.println(resultSet.getDate(1));
	    stmt.close();
	}
	
	public Double getAverageOccupationLevel(String startDate, String endDate, String roomNumber) throws SQLException{
		String query = "begin ? := getAvgOccupLvl(TO_DATE(?, 'dd/mm/yyyy'), TO_DATE(?, 'dd/mm/yyyy'), CAST(? AS INT)); end;";
		CallableStatement stmt = conn.prepareCall(query);
		stmt.registerOutParameter(1, Types.DOUBLE);
		stmt.setString(2, startDate);
		stmt.setString(3, endDate);
		stmt.setString(4, roomNumber);
		stmt.execute();
		return (Double)stmt.getObject(1);
	}
	
	public Double getTotalOccupation(String startDate, String endDate) throws SQLException{
		String query = "begin ? := getTotalOccupation(TO_DATE(?, 'dd/mm/yyyy'), TO_DATE(?, 'dd/mm/yyyy')); end;";
		CallableStatement stmt = conn.prepareCall(query);
		stmt.registerOutParameter(1, Types.DOUBLE);
		stmt.setString(2, startDate);
		stmt.setString(3, endDate);
		stmt.execute();
		return (Double)stmt.getObject(1);
	}
	
	public Integer getMostRequiredRoom(String month, String year) throws SQLException{
		String query = "begin ? := getMostRequiredRoom(?, ?); end;";
		CallableStatement stmt = conn.prepareCall(query);
		stmt.registerOutParameter(1, Types.INTEGER);
		stmt.setString(2, month);
		stmt.setString(3, year);
		stmt.execute();
		return (Integer)stmt.getObject(1);
	}
	
	public void showOccupiedRooms(String startDate, String endDate) throws SQLException {
		String query = "SELECT * FROM TABLE(getRoomWithOccup(TO_DATE(?, 'dd/mm/yyyy'), TO_DATE(?, 'dd/mm/yyyy')))";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, startDate);
		stmt.setString(2, endDate);
		ResultSet result = stmt.executeQuery();
		System.out.println("RoomNum | QtyOfPeople");
		while ( result.next() ) {
			System.out.println(result.getInt(1) + " | " + result.getInt(2));
		}
	}
	
	public void showClients(int orderOpt) throws SQLException {
		String query = "SELECT * FROM Person ORDER BY ?";
		PreparedStatement stmt = conn.prepareStatement(query);
		if (orderOpt == 1)
			stmt.setString(1, "DocNum");
		else if (orderOpt == 2)
			stmt.setString(2, "Surname");
		else if (orderOpt == 3)
			stmt.setString(2, "DOB");
		ResultSet result = stmt.executeQuery();
		System.out.println("DocType | DocNum | Name | Surname | DOB | Sex | Email | PhoneNum");
		while ( result.next() ) {
			System.out.println(result.getString(1) + " | " + result.getString(2) + " | " + result.getString(3) + " | " + result.getString(4)
					 + " | " + result.getDate(5) + " | " + result.getString(6)  + " | " + result.getString(7) + " | " + result.getString(8));
		}
	}
	
	public void showReserveHistory(String docType, String docNum) throws SQLException {
		String query = "SELECT * FROM Reserve WHERE DocType = ? AND DocNum = ? AND EndDate < SYSDATE";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, docType);
		stmt.setString(2, docNum);
		ResultSet result = stmt.executeQuery();
		System.out.println("DocType | DocNum | StartDate | EndDate | RoomNum");
		while ( result.next() ) {
			System.out.println(result.getString(1) + " | " + result.getString(2) + " | " + result.getDate(3)
					+ " | " + result.getDate(4) + " | " + result.getInt(5));
		}
	}
	
	public void showAvailableRooms(String startDate, String endDate, String roomType) throws SQLException {
		String query = "SELECT * FROM TABLE(getAvRoomsByDateAndRoomType(TO_DATE(?, 'dd/mm/yyyy'), TO_DATE(?, 'dd/mm/yyyy'), ?))";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, startDate);
		stmt.setString(2, endDate);
		stmt.setString(3, roomType);
		ResultSet result = stmt.executeQuery();
		System.out.println("RoomNum | RoomType | Capacity | Plus");
		while ( result.next() ) {
			System.out.println(result.getInt(1) + " | " + result.getString(2) + " | " + result.getInt(3)
					+ " | " + result.getDouble(4));
		}
	}
	
	public void registerClient(String docType, String docNum, String name, String surname, String dob, String sex, String email, String phoneNum) throws SQLException{
		String query = "INSERT INTO Person values(?,?,?,?,TO_DATE(?, 'dd/mm/yyyy'),?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, docType);
		stmt.setString(2, docNum);
		stmt.setString(3, name);
		stmt.setString(4, surname);
		stmt.setString(5, dob);
		stmt.setString(6, sex);
		stmt.setString(7, email);
		stmt.setString(8, phoneNum);
		stmt.execute();
	}
	
	public void registerReservation(String docType, String docNum, String startDate, String endDate, String roomNum) throws SQLException{
		String query = "INSERT INTO Reserve values (?,?,TO_DATE(?, 'dd/mm/yyyy'),TO_DATE(?, 'dd/mm/yyyy'),CAST(? AS INT))";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, docType);
		stmt.setString(2, docNum);
		stmt.setString(3, startDate);
		stmt.setString(4, endDate);
		stmt.setString(5, roomNum);
		stmt.execute();
	}
	
	public void updateRoom(String roomNum, String roomType, String plus, String available) throws SQLException {
		String query = "UPDATE Room SET RoomType = ?, Plus = CAST(? AS FLOAT), Available = ? WHERE RoomNum = CAST(? AS INT)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, roomType);
		stmt.setString(2, plus);
		stmt.setString(3, available);
		stmt.setString(4, roomNum);
		stmt.execute();
	}
}
